#!/usr/bin/env python
#
#   urldb.py - URL database
#   
#
from dictdb import DictDB, EmptyValueError, DuplicateKeyValueError

class UrlDB(DictDB):
    '''
    Database for URLs
    '''
    def __init__(self, db):
        ''' 
        init 
        '''
        fields = {
            # url string
            'url': {'type': 'text', 'index': 'unique'},    
            # None, 'EDGE', 'CHECK_ONCE'
            'type': {'type': 'text'},
            'md5': {'type': 'text'},      
            'status': {'type': 'text'},
        }
        
        DictDB.__init__(self, db, fields)

        SQL =  '''CREATE VIEW IF NOT EXISTS urldb AS 
                    SELECT 
                        entity.id AS id, url_data.value AS url, type_data.value AS type, 
                        status_data.value AS status, md5_data.value AS md5 
                    FROM entity 
                    LEFT JOIN url_data ON (entity.id = url_data.id) 
                    LEFT JOIN type_data ON (entity.id = type_data.id) 
                    LEFT JOIN status_data ON (entity.id = status_data.id) 
                    LEFT JOIN md5_data ON (entity.id = md5_data.id);'''
        self.sql(SQL)

    def add(self, url, url_type):
        '''
        add url to database
        '''
        entity_id = self.add_entity()
        if entity_id:
            try:
                self.add_key_value(entity_id, key='url', value=url)
                self.add_key_value(entity_id, key='type', value=url_type)
            except DuplicateKeyValueError:
                self.delete_entity(entity_id)
                return None
        return entity_id
    
    def update(self, url, **kwargs):
        '''
        update url info
        '''
        for k in kwargs.keys():
            try:
                entity_id = self.sql("SELECT id FROM url_data WHERE value='%s'" % url)[0][0]
            except IndexError:
                return None 
                   
            try:
                t_id = self.sql("SELECT id FROM %s_data WHERE id=%d" % (k, entity_id))[0][0]
            except IndexError:
                self.sql("INSERT INTO %s_data (id, value) VALUES (%d, '%s')" % (k, entity_id, kwargs[k]))

            SQL = "UPDATE %s_data SET value='%s' WHERE id = %d" % (k, kwargs[k], entity_id)
            self.sql(SQL)

            return entity_id
    
    def select(self, where='', limit=0):
        '''
        filter url by criteria
        '''        
        return  DictDB.select(self, keys=['url', 'type', 'status', 'md5'], where=where, limit=limit)

    def stat(self):
        '''
        statistics for urldb
        '''
        stat = {}
        stat["total"] = self.sql("SELECT count(*) FROM url_data")[0][0]
        stat["checked"] = self.sql("SELECT count(*) FROM status_data WHERE value = 'checked'")[0][0]
        stat["edge"] = self.sql("SELECT count(*) FROM type_data WHERE value = 'EDGE' or value = 'edge'")[0][0]
        return stat

    def load_from_json(self, filename):
        '''
        load urls_db from json file
        '''
        import json
        try:
            db = json.load(open(filename, 'rb'))
            for url in db.keys():
                entity_id = self.add_entity()
                if entity_id:
                    try:
                        self.add_key_value(entity_id, key='url', value=url)
                    except DuplicateKeyValueError:
                        self.delete_entity(entity_id)
                        continue
                        
                    for p_url in db[url].keys():
                        try:
                            self.add_key_value(entity_id, key=p_url, value=db[url][p_url])
                        except EmptyValueError:
                            pass
                        except DuplicateKeyValueError:
                            pass
            self.sync()
        except IOError:
            return False
        return True

if __name__ == "__main__":

    from optparse import OptionParser
    import sys
	
    parser = OptionParser()
    parser.add_option("-d", "--db", dest="db", help="url database file (sqlite3)")
    parser.add_option("-j", "--json", dest="json", help="import data file json file")
    (options, args) = parser.parse_args()

    if options.db == None:
        parser.error("Incorrect number of arguments, \n\nuse -h or --help for detail\n")
        sys.exit()

    urls_db = UrlDB(options.db)

    if options.json:
        urls_db.load_from_json(options.json)
    
